package com.neusoft.dao.impl;

import com.neusoft.dao.EmpDao;
import com.neusoft.pojo.Dept;
import com.neusoft.pojo.Emp;
import com.neusoft.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class EmpDaoImpl implements EmpDao {
    @Override
    public List<Emp> findEmpByDept() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Emp> emps = new ArrayList();
        Emp emp = null;
        Dept dept = null;
        try {
            conn = DBUtil.getConn();
            pstmt = conn.prepareStatement("select e.ename,e.hiredate,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno");
            rs = pstmt.executeQuery();
            while(rs.next()){
                emp = new Emp();
                emp.setEname(rs.getString("ename"));
                emp.setHiredate(rs.getDate("hiredate"));
                dept = new Dept();
                dept.setDname(rs.getString("dname"));
                dept.setLoc(rs.getString("loc"));
                emp.setDept(dept);
                emps.add(emp);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(rs,pstmt,conn);
        }


        return emps;
    }

    @Override
    public List<Emp> findAll(String keyword) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Emp> emps = new ArrayList();
        Emp emp = null;

        try {
            conn = DBUtil.getConn();
            pstmt = conn.prepareStatement("select * from emp where ename like '%"+keyword+"%'");
            rs = pstmt.executeQuery();
            while (rs.next()){
                emp = new Emp();
                emp.setEname(rs.getString("ename"));
                emp.setJob(rs.getString("job"));
                emps.add(emp);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBUtil.closeAll(rs,pstmt,conn);
        }


        return emps;
    }

    @Override
    public List<Emp> findAll(String keyword1, String keyword2) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Emp> emps = new ArrayList();
        Emp emp = null;
        StringBuilder sql = new StringBuilder("select * from emp where 1=1");
        if(keyword1!=null&&!keyword1.equals("")){
            sql.append(" and ename like '%"+keyword1+"%'");
        }
        if(keyword2!=null&&!keyword2.equals("")){
            sql.append(" and job like '%"+keyword2+"%'");
        }
        try {
            conn = DBUtil.getConn();
            pstmt = conn.prepareStatement(sql.toString());
            rs = pstmt.executeQuery();
            while (rs.next()){
                emp = new Emp();
                emp.setEname(rs.getString("ename"));
                emp.setJob(rs.getString("job"));
                emps.add(emp);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBUtil.closeAll(rs,pstmt,conn);
        }


        return emps;
    }

    @Override
    public List<Emp> findAll() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Emp> emps = new ArrayList();
        Emp emp = null;

        try {
            conn = DBUtil.getConn();
            pstmt = conn.prepareStatement("select * from emp");
            rs = pstmt.executeQuery();
            while(rs.next()){
                emp = new Emp();
                emp.setEmpno(rs.getInt("empno"));
                emp.setEname(rs.getString("ename"));
                emp.setHiredate(rs.getDate("hiredate"));
                emp.setJob(rs.getString("job"));
                emps.add(emp);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(rs,pstmt,conn);
        }
        return emps;
    }
}
